package ru.fadeev.crammer;

import java.sql.*;

/**
 * DatabaseManager class contains some common functions 
 * for working with database.
 * @author Konstantin Fadeyev
 *
 */
public class DatabaseManager {

	/**
	 * Returns connection to embedded database (hsqldb)
	 * @return connection object
	 * @throws SQLException if connecting to database failed
	 */
	public static Connection getConnection() throws SQLException {
		try {
			Class.forName("org.hsqldb.jdbcDriver" );
		} catch(Exception e) {
			e.printStackTrace();
		}
		String url ="jdbc:hsqldb:file:db/crammer";
	    return DriverManager.getConnection(url, "sa", "");	    
	}
	
	
	/**
	 * Checks if all tables in database exists. If any doesn't, creates it.
	 * @throws SQLException if connecting to database failed
	 */
	public static void checkDatabaseConsistency() throws SQLException {
        Connection connection = getConnection();
        
        ResultSet resultSet;
        Statement statement;
        DatabaseMetaData metaData = connection.getMetaData();
       
        // Creating if doesn't exists DICTIONARY table
        resultSet = 
        	metaData.getTables(null, null, "DICTIONARY", 
        			new String[]{"TABLE"});
        
        if(!resultSet.next()) {
        	statement = connection.createStatement();
        	resultSet = statement.executeQuery(
        			"CREATE CACHED TABLE DICTIONARY (" +
        				"ID INTEGER GENERATED BY DEFAULT AS IDENTITY, " +
        				"NAME CHAR(" + Settings.DICTIONARY_NAME_MAX_LENGTH + "), " +
        				"TO_TEST BOOLEAN DEFAULT FALSE, " +
        				"ORDER_NUMBER INTEGER" +
        			")"
        			);	
        	statement.close();
        }

        // Creating if doesn't exists SHEET table
        resultSet = metaData.getTables(null, null, "SHEET", 
        		new String[]{"TABLE"});
        
        if(!resultSet.next()) {
        	statement = connection.createStatement();
        	resultSet = statement.executeQuery(
        			"CREATE CACHED TABLE SHEET (" +
        				"ID INTEGER GENERATED BY DEFAULT AS IDENTITY, " +
        				"DICT_ID INTEGER NOT NULL, " +
        				"NAME CHAR(" + Settings.SHEET_NAME_MAX_LENGTH + "), " +
        				"TO_TEST BOOLEAN DEFAULT FALSE, " +
        				"ORDER_NUMBER INTEGER" +

        			")"
        			);	
        	statement.close();
        }

        // Creating if doesn't exists WORD table
        resultSet = metaData.getTables(null, null, "WORD_RECORD", 
        		new String[]{"TABLE"});
        
        if(!resultSet.next()) {
        	statement = connection.createStatement();
        	resultSet = statement.executeQuery(
        			"CREATE CACHED TABLE WORD_RECORD (" +
        				"ID INTEGER GENERATED BY DEFAULT AS IDENTITY, " +
        				"SHEET_ID INTEGER NOT NULL, " +
        				"WORD CHAR("+ Settings.WORD_MAX_LENGTH +"), " +
        				"TRANSCRIPTION CHAR("+ Settings.TRANSCRIPTION_MAX_LENGTH +"), " +
        				"TRANSLATION CHAR("+ Settings.TRANSLATION_MAX_LENGTH +"), " +
        				"PROMPT CHAR("+ Settings.PROMPT_MAX_LENGTH +"), " +
        				"CREATE_DATE DATETIME, " +
        				"ORDER_NUMBER INTEGER" +
        			")"
        			);	
        	statement.close();
        }

        // Creating if doesn't exists TEST_HISTORY table
        resultSet = metaData.getTables(null, null, "TEST_HISTORY", 
        		new String[]{"TABLE"});
        
        if(!resultSet.next()) {
        	statement = connection.createStatement();
        	resultSet = statement.executeQuery(
        			"CREATE CACHED TABLE TEST_HISTORY (" +
        				"ID INTEGER GENERATED BY DEFAULT AS IDENTITY, " +
        				"WORD_ID INTEGER NOT NULL, " +
        				"SUCCESSFUL BOOLEAN, " +
        				"TEST_TYPE INTEGER, " +
        				"CREATE_DATE DATETIME " +
        			")"
        			);	
        	statement.close();
        }
        
        // Creating if doesn't exists PHRASES_BATCH table
        resultSet = metaData.getTables(null, null, "PHRASES_BATCH", 
        		new String[]{"TABLE"});
        
        if(!resultSet.next()) {
        	statement = connection.createStatement();
        	resultSet = statement.executeQuery(
        			"CREATE CACHED TABLE PHRASES_BATCH (" +
        				"ID INTEGER GENERATED BY DEFAULT AS IDENTITY, " +
        				"NAME CHAR("+ Settings.BATCH_NAME_MAX_LENGTH +"), " +
        				"CREATE_DATE DATETIME" +
        			")"
        			);	
        	statement.close();
        }
        
        // Creating if doesn't exists PHRASE table
        resultSet = metaData.getTables(null, null, "PHRASE", 
        		new String[]{"TABLE"});
        
        if(!resultSet.next()) {
        	statement = connection.createStatement();
        	statement.executeQuery(
        			"CREATE CACHED TABLE PHRASE (" +
        				"ID INTEGER GENERATED BY DEFAULT AS IDENTITY, " +
        				"BATCH_ID INTEGER NOT NULL, " +
        				"PHRASE_TEXT VARCHAR("+ Settings.PHRASE_MAX_LENGTH +"), " +
        				"FOREIGN KEY (BATCH_ID) REFERENCES PHRASES_BATCH"+
        			")"
        			);	
        	
        	statement.executeQuery(
        			"CREATE INDEX PHRASE_INDEX ON PHRASE (" +
        				"BATCH_ID, PHRASE_TEXT" +	        				
        			")"
        			);	
        	statement.close();
        }
        
        // Creating if doesn't exists PHRASE_WORD table
        resultSet = metaData.getTables(null, null, "PHRASE_WORD", 
        		new String[]{"TABLE"});
        
        if(!resultSet.next()) {
        	statement = connection.createStatement();
        	statement.executeQuery(
        			"CREATE CACHED TABLE PHRASE_WORD (" +
        				"WORD_ID INTEGER NOT NULL, " +
        				"PHRASE_ID INTEGER NOT NULL, " +
        				"TEST_COUNT INTEGER DEFAULT 0  NOT NULL, " +
        				"FOREIGN KEY (WORD_ID) REFERENCES WORD_RECORD,"+
        				"FOREIGN KEY (PHRASE_ID) REFERENCES PHRASE"+
        			")"
        			);	
        	
        	statement.executeQuery(
        			"CREATE INDEX PHRASE_WORD_INDEX ON PHRASE_WORD (" +
        				"WORD_ID, PHRASE_ID" +	        				
        			")"
        			);	
        	statement.close();
        }
        
        // Creating if doesn't exists INDEXER_TASK table
        resultSet = metaData.getTables(null, null, "INDEXER_TASK", 
        		new String[]{"TABLE"});
        
        if(!resultSet.next()) {
        	statement = connection.createStatement();
        	statement.executeQuery(
        			"CREATE CACHED TABLE INDEXER_TASK (" +
    					"ID INTEGER GENERATED BY DEFAULT AS IDENTITY, " +
        				"TASK_ID INTEGER NOT NULL, " +
        				"PHRASE BOOLEAN, " +
        				"INSERTED BOOLEAN, " +
        				"TASK_TEXT VARCHAR("+ Settings.PHRASE_MAX_LENGTH +")" +
        			")"
        			);	        	
        	statement.close();
        }
        
        // Creating if doesn't exists PHB_TASK table
        resultSet = metaData.getTables(null, null, "PHB_TASK", 
        		new String[]{"TABLE"});
        
        if(!resultSet.next()) {
        	statement = connection.createStatement();
        	statement.executeQuery(
        			"CREATE CACHED TABLE PHB_TASK (" +
    					"TASK_NUMBER INTEGER, " +
        				"WORD VARCHAR("+ Settings.WORD_MAX_LENGTH +")" +
        			")"
        			);	        	
        	statement.close();
        }
        
        // Creating if doesn't exists SETTINGS table
        resultSet = metaData.getTables(null, null, "SETTINGS", 
        		new String[]{"TABLE"});
        
        if(!resultSet.next()) {
        	statement = connection.createStatement();
        	resultSet = statement.executeQuery(
        			"CREATE CACHED TABLE SETTINGS (" +
        				"NAME CHAR("+ Settings.SETTINGS_KEY_MAX_LENGTH +"), " +
        				"VALUE VARCHAR("+ Settings.SETTINGS_VALUE_MAX_LENGTH +")" +
        			")"
        			);	
        	statement.close();
        	
        	// Filling table with default settings
        	Settings.saveDefaults();
        }

        connection.close();
	}
	
	
	/**
	 * Shuts down database, saving all data on hard drive.
	 */
	public static void shutdownDB() {
		try {
	        Connection connection = getConnection();
			Statement statement = connection.createStatement();
			statement.executeQuery("SHUTDOWN");
			statement.close();
	        connection.close();
	    } catch (Exception e) {
	        e.printStackTrace();
	    }
	}
}
